﻿using DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using DBUtility;
/*************************************************************************************

     拆包相关操作  liudan add
  
*************************************************************************************/
namespace DAL
{
    public class LockStoreDAL 
    {
        /// <summary>
        /// 获取锁库单
        /// </summary>
        public DataSet GetLockStoreBill()
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append("   select b.FBrNo,b.PkgsBarCode,c.HelperCode as HelperCode,a.PlaceBarCode,(case when  b.sxnum%2=0 then '◆' when b.sxnum%2 <> 0 then '★' else '' end) stype,a.FName PlaceName, b.WorkBillNo,b.FStatus,d.FStatusID ");
            commandText.Append(" from LockStoreBill b left join t_StockPlace a on a.FSPID = PosiID   ");
            commandText.Append(" left join stk_Package c on c.PkgsID = b.PkgsID  ");
            commandText.Append(" left join ICMO d on b.WorkBillNo = d.FInterID ");
            commandText.Append(" where  d.FStatusID ='2' and b.FStatus ='1' order by sxnum desc");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 扫码获取明细信息
        /// </summary>
        public bool GetLockStore(string PkgsBarCode, out string FBrNo, out string HelperCode, out string PlaceBarCode, out string PlaceName, out string WorkBillNo)
        {
            bool lb_return = false;
            //string FBrNo = ""; string PlaceBarCode = ""; string PlaceName = ""; string HelperCode = ""; string WorkBillNo = "";
            FBrNo = ""; PlaceBarCode = ""; PlaceName = ""; HelperCode = ""; WorkBillNo = "";

            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append("  select a.FBrNo,c.HelperCode,b.PlaceBarCode,b.FName PlaceName ,a.WorkBillNo   ");
            commandText.Append("  from LockStoreBill a  ");
            commandText.Append("  left join t_StockPlace b on b.FParentID=a.StockID and b.FSPID=a.PosiID  ");
            commandText.Append("  left join stk_Package c on c.PkgsID = a.PkgsID ");
            commandText.Append("  where a.FStatus = 1  ");
            commandText.Append("  and a.PkgsBarCode = '" + PkgsBarCode + "' ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                lb_return = true;

                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    FBrNo = dr["FBrNo"].ToString().Trim();
                    PlaceBarCode = dr["PlaceBarCode"].ToString().Trim();
                    PlaceName = dr["PlaceName"].ToString().Trim();
                    HelperCode = dr["HelperCode"].ToString().Trim();
                    WorkBillNo = dr["WorkBillNo"].ToString().Trim();
                }
            }

            return lb_return;
        }
        /// <summary>
        /// 审核锁库单
        /// </summary>
        public bool ExamineLockStore(string FBrNo, string PkgsBarCode, string WorkBillNo)
        {
            bool lb_return = false;
            string sql2 = "", sql1 = ""; int cknnum = 0;
            StringBuilder commandText = new StringBuilder();
            List<string> sql = new List<string>();
            //查询预出库的要出库多少，不包含替换，未找到，退件，
            sql1 = "select  count(PosiID) sPosiID,PosiID,PkgsBarCode  from LockStoreBill where WorkBillNo='" + WorkBillNo + "' and FStatus='1'  and Sxnum is not null  group by PosiID,PkgsBarCode order by PosiID";
            DataTable deco = DbHelperSQL.QueryTable(sql1);
            if (deco.Rows.Count > 0)
            {
                //查询库存某库位是否是一整拖出库
                sql2 = "select count(PosiID) as PosiID from stk_Package where PosiID in (select PosiID from LockStoreBill where WorkBillNo='" + WorkBillNo + "' and Sxnum is not null) and PkgsStatus not in (55,99,2,3,4,5,6)";
                DataTable der = DbHelperSQL.QueryTable(sql2);
                if (der.Rows.Count > 0)
                {
                    cknnum = int.Parse(der.Rows[0]["PosiID"].ToString());//库存数量
                }
                //查询预出库是否与库存的仓位拖相等
                if (deco.Rows.Count.Equals(cknnum))
                {
                    for (int i = 0; i < deco.Rows.Count; i++)
                    {
                        sql.Add("update LockStoreBill set Fstatus=2 where  WorkBillNo = '" + WorkBillNo + "' and   PkgsBarCode ='" + deco.Rows[i]["PkgsBarCode"].ToString() + "'");
                        sql.Add(" update stk_Package set PkgsStatus=10 where PkgsBarCode='" + deco.Rows[i]["PkgsBarCode"].ToString() + "' and PkgsStatus = 1 ");

                    }
                }
                else
                {
                    sql.Add("update LockStoreBill set Fstatus=2 where  FBrNo = '" + FBrNo + "'");
                    sql.Add(" update stk_Package set PkgsStatus=10 where PkgsBarCode='" + PkgsBarCode + "' and PkgsStatus = 1 ");
                }
            }
            else
            {
                sql.Add(" update LockStoreBill set  Fstatus=2 where FBrNo='" + FBrNo + "'");
                sql.Add(" update stk_Package set PkgsStatus=10 where PkgsBarCode='" + PkgsBarCode + "' and PkgsStatus = 1 ");
            }
           
          
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                int i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                { lb_return = true; }
                else { lb_return = false; }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lb_return;
        }


    }
}
